# -*- coding: utf-8 -*-
'''
| @author: olivia.dou
| Created on: 2020/1/21 11:27
| desc: 模板相关功能

'''
import logging, traceback
import pandas as pd
from util.db_util import execute_query

FORMULARS={1:"abs"}

def __init():
    pass


def get_code_of_biz_in_template(client_code, template_code, template_fin_item_id):
    """从指定模板中找到科目所在业务的编码

    :param client_code: 客户编码
    :param template_code: 模板编码
    :param template_fin_item_id: 模板科目id

    :return: 指定模板中指定科目所在业务的编码
    """

    try:
        sql = """SELECT b.biz_code FROM `t_das_std_template_fin_item` f
            INNER JOIN t_das_std_template_biz b
            on f.template_biz_id = b.id
            where f.id=%s
            and b.client_code = '%s' and b.template_code='%s'"""%(template_fin_item_id, client_code, template_code)

        res = execute_query(sql)
        return res[0][0]

    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return ""

def get_fin_item_info(template_fin_item_id):
    """根据t_das_std_template_fin_item表id获取标准化科目code

    :param template_fin_item_id: 模板科目id

    :return: 指定模板科目的标准化编码
    """

    sql = "select i.id,i.std_code from t_das_std_template_fin_item ti inner join t_das_std_fin_item i " \
          "on ti.fin_item_id = i.id where ti.id=%s"%template_fin_item_id

    try:
        res = execute_query(sql)
        data = pd.Series(res[0],index = ['id','std_code'])
        return data
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return pd.Series([])

def get_std_template_code(template_id):
    """获取模板的client_code,code

    :param template_id: 模板id

    :return: 客户编码,模板编码的元组
    """

    sql = "select client_code,code from t_das_std_template where id=%s"%template_id
    try:
        res = execute_query(sql)
        return res[0]
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return () #empty tuple



def get_std_template_id(code):
    """根据模板编码查询模板定义id

    :param code: 模板编码

    :return: 模板id
    """
    results = execute_query(
        "SELECT id FROM t_das_std_template WHERE `code`='%s'" % code)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_template_biz_id(template_code, client_code, biz_code):
    """根据模板与业务编码查询模板业务绑定id

    :param template_code: 模板编码
    :param client_code: 客户编码
    :param biz_code: 业务编码

    :return: 模板业务绑定id
    """

    results = execute_query(
        "SELECT id FROM t_das_std_template_biz WHERE template_code='%s' AND client_code='%s' AND biz_code='%s'" % (
        template_code, client_code, biz_code))
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_template_fin_item_id(template_code, client_code,biz_code,std_code):
    """根据模板与科目编码查询模板科目绑定id

    :param template_code: 模板编码
    :param client_code: 客户编码
    :param biz_code: 业务编码
    :param std_code: 科目编码

    :return: 模板科目绑定id
    """

    results = execute_query(
        "SELECT id FROM t_das_std_template_fin_item WHERE template_biz_id=(SELECT id FROM t_das_std_template_biz WHERE template_code='%s' AND client_code='%s' AND biz_code='%s') AND fin_item_id=(SELECT id FROM t_das_std_fin_item WHERE std_code='%s') AND client_code='%s'" % (
            template_code,client_code,biz_code,std_code,client_code))
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def get_template_fin_item_info(tmpl_fin_item_id):
    """获取template_fin_item信息
    
    :param tmpl_fin_item_id: template_fin_item id
     
    :return: 指定id的template_fin_item信息
    """

    sql = """SELECT * FROM t_das_std_template_fin_item WHERE id=%s"""%tmpl_fin_item_id
    res = execute_query(sql,result_type="dict")

    try:
        return res[0]

    except Exception as e:
        logging.error("get_std_template_fin_item_info failure：%s"%e)
        return None


